package com.sailfish.springbootdemo.dao.db2;

import com.sailfish.springbootdemo.common.DB2TableName;
import com.sailfish.springbootdemo.pojo.db2.PcbCapacityData;
import com.sailfish.springbootdemo.pojo.db2.PcbCapacityParam;
import com.sailfish.springbootdemo.pojo.db2.PcbCapacityParamHistory;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

public interface PcbCapacityParamHistoryDao {

    @Results(id = "pcbCapacityParamHistoryResultMap", value = {
            @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER),
            @Result(column = "version", property = "version", jdbcType = JdbcType.INTEGER),
            @Result(column = "pcb_capacity_param_id", property = "pcbCapacityParamId", jdbcType = JdbcType.INTEGER),
            @Result(column = "parent_id", property = "parentId", jdbcType = JdbcType.INTEGER),
            @Result(column = "pcb_capacity_param_name", property = "pcbCapacityParamName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "pcb_capacity_param_name_en", property = "pcbCapacityParamNameEn", jdbcType = JdbcType.VARCHAR),
            @Result(column = "index_link", property = "indexLink", jdbcType = JdbcType.VARCHAR),
            @Result(column = "level", property = "level", jdbcType = JdbcType.INTEGER),
            @Result(column = "pcb_capacity_param_key", property = "pcbCapacityParamKey", jdbcType = JdbcType.VARCHAR),
            @Result(column = "pcb_capacity_param_key2", property = "pcbCapacityParamKey2", jdbcType = JdbcType.VARCHAR),
            @Result(column = "pcb_capacity_param_key3", property = "pcbCapacityParamKey3", jdbcType = JdbcType.VARCHAR),
            @Result(column = "pcb_capacity_param_key4", property = "pcbCapacityParamKey4", jdbcType = JdbcType.VARCHAR),
            @Result(column = "is_required", property = "isRequired", jdbcType = JdbcType.INTEGER),
            @Result(column = "param_type", property = "paramType", jdbcType = JdbcType.INTEGER),
            @Result(column = "param_options", property = "paramOptions", jdbcType = JdbcType.VARCHAR),
            @Result(column = "rule_type", property = "ruleType", jdbcType = JdbcType.INTEGER),
            @Result(column = "unit", property = "unit", jdbcType = JdbcType.VARCHAR),
            @Result(column = "tip", property = "tip", jdbcType = JdbcType.VARCHAR),
            @Result(column = "remark", property = "remark", jdbcType = JdbcType.VARCHAR),
            @Result(column = "remark_pic", property = "remarkPic", jdbcType = JdbcType.VARCHAR),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "status", property = "status", jdbcType = JdbcType.INTEGER),
            @Result(column = "sort", property = "sort", jdbcType = JdbcType.INTEGER),
            @Result(column = "create_user", property = "createUser", jdbcType = JdbcType.VARCHAR),
            @Result(column = "update_user", property = "updateUser", jdbcType = JdbcType.VARCHAR),
    })

    /*
     * 获取id下的所有子节点
     */
    @Select("<script> " +
            "select *" +
            " from " + DB2TableName.PCB_CAPAICTY_PARAM_HISTORY +
            " <where>  " +
            "<if test='id != null'>" +
            " and (FIND_IN_SET(pcb_capacity_param_id,get_child_list_history(#{id},#{version})))" +
            "</if>" +
            "<if test='version != null'>" +
            " and (version = #{version})" +
            "</if>" +
            " </where>  " +
            " order by sort" +
            "</script>")
    List<PcbCapacityParamHistory> getAllByIdLower(@Param("id") Integer id, @Param("version") String version);

    /*
     *查询单个
     */
    @Select("<script> " +
            "select * " +
            " from " + DB2TableName.PCB_CAPAICTY_PARAM_HISTORY +
            " where pcb_capacity_param_id = #{id} and version = #{version} " +
            "</script>")
    @ResultMap(value = "pcbCapacityParamHistoryResultMap")
    PcbCapacityParamHistory getById(@Param("id") Integer id, @Param("version") String version);

    /*
     *查询参数列表
     */
    @Select("<script> " +
            "select id, version, pcb_capacity_param_id, parent_id, pcb_capacity_param_name, pcb_capacity_param_name_en, index_link, pcb_capacity_param_key, pcb_capacity_param_key2, pcb_capacity_param_key3, pcb_capacity_param_key4,level, is_required, param_type, param_options, rule_type, unit, tip, remark, remark_pic, create_user, create_time, update_user, update_time, status, sort " +
            " from " + DB2TableName.PCB_CAPAICTY_PARAM_HISTORY +
            " where `level`= #{level}" +
            "<if test='link != null and link != \"\"'>" +
            " and (index_link like CONCAT(#{link},'%'))" +
            "</if>" +
            "<if test='paramname != null and paramname != \"\"'>" +
            " and (pcb_capacity_param_name like CONCAT('%', #{paramname},'%') or pcb_capacity_param_name_en like CONCAT('%',#{paramname},'%')  )" +
            "</if>" +
            "<if test='version != null and version != \"\"'>" +
            " and (version = #{version} )" +
            "</if>" +
            " order by sort" +
            "</script>")
    @ResultMap(value = "pcbCapacityParamHistoryResultMap")
    List<PcbCapacityParamHistory> getParamList(@Param("link") String link, @Param("level") Integer levelD,
                                               @Param("paramname") String paramname, @Param("version") String version);

    /*
     * 查询指定行数据
     */
    @Select("<script> " +
            "select id, version, pcb_capacity_param_id, parent_id, pcb_capacity_param_name, pcb_capacity_param_name_en, index_link, pcb_capacity_param_key, pcb_capacity_param_key2, pcb_capacity_param_key3, pcb_capacity_param_key4, level, is_required, param_type, param_options, rule_type, unit, tip, remark, remark_pic, create_user, create_time, update_user, update_time, status, sort " +
            " from " + DB2TableName.PCB_CAPAICTY_PARAM_HISTORY +
            "<where>" +
            "<if test='parentId != null'>" +
            " and parent_id = #{parentId}" +
            "</if>" +
            "<if test='version != null'>" +
            " and version = #{version}" +
            "</if>" +
            "<if test='pcbCapacityParamName != null and pcbCapacityParamName != \"\"'>" +
            " and pcb_capacity_param_name = #{pcbCapacityParamName}" +
            "</if>" +
            "<if test='pcbCapacityParamNameEn != null and pcbCapacityParamNameEn != \"\"'>" +
            " and pcb_capacity_param_name_en = #{pcbCapacityParamNameEn}" +
            "</if>" +
            "<if test='indexLink != null and indexLink != \"\"'>" +
            " and index_link = #{indexLink}" +
            "</if>" +
            "<if test='pcbCapacityParamKey != null and pcbCapacityParamKey != \"\"'>" +
            " and pcb_capacity_param_key = #{pcbCapacityParamKey} " +
            "</if>" +
            "<if test='pcbCapacityParamKey2 != null and pcbCapacityParamKey2 != \"\"'>" +
            " and pcb_capacity_param_key2 = #{pcbCapacityParamKey2} " +
            "</if>" +
            "<if test='pcbCapacityParamKey3 != null and pcbCapacityParamKey3 != \"\"'>" +
            " and pcb_capacity_param_key3 = #{pcbCapacityParamKey3} " +
            "</if>" +
            "<if test='pcbCapacityParamKey4 != null and pcbCapacityParamKey4 != \"\"'>" +
            " and pcb_capacity_param_key4 = #{pcbCapacityParamKey4} " +
            "</if>" +
            "<if test='level != null '>" +
            " and level = #{level}" +
            "</if>" +
            "<if test='isRequired != null '>" +
            " and is_required = #{isRequired}" +
            "</if>" +
            "<if test='paramType != null '>" +
            " and param_type = #{paramType}" +
            "</if>" +
            "<if test='paramOptions != null and paramOptions != \"\"'>" +
            " and param_options = #{paramOptions}" +
            "</if>" +
            "<if test='status != null '>" +
            " and status = #{status}" +
            "</if>" +
            "<if test='sort != null '>" +
            " and sort = #{sort} " +
            "</if>" +
            "</where>" +
            "</script>")
    @ResultMap(value = "pcbCapacityParamHistoryResultMap")
    List<PcbCapacityParamHistory> getAllByLimit(Integer parentId, String pcbCapacityParamName, String pcbCapacityParamNameEn,
                                                String indexLink, String pcbCapacityParamKey, String pcbCapacityParamKey2,
                                                String pcbCapacityParamKey3, String pcbCapacityParamKey4, Integer level, String version);

    @Select("<script> " +
            "select id,version,pcb_capacity_param_id,parent_id,pcb_capacity_param_name,pcb_capacity_param_name_en,index_link,pcb_capacity_param_key,pcb_capacity_param_key2,pcb_capacity_param_key3,pcb_capacity_param_key4,level,is_required,param_type,param_options,rule_type,unit,tip,remark, remark_pic,create_user,create_time,update_user,update_time,status,sort " +
            " from " + DB2TableName.PCB_CAPAICTY_PARAM_HISTORY +
            "<where>" +
            "<if test='version != null'>" +
            " and version = #{version}" +
            "</if>" +
            "<if test='parentId != null'>" +
            " and parent_id = #{parentId}" +
            "</if>" +
            "<if test='pcbCapacityParamName != null and pcbCapacityParamName != \"\"'>" +
            " and pcb_capacity_param_name = #{pcbCapacityParamName}" +
            "</if>" +
            "<if test='pcbCapacityParamNameEn != null and pcbCapacityParamNameEn != \"\"'>" +
            " and pcb_capacity_param_name_en = #{pcbCapacityParamNameEn}" +
            "</if>" +
            "<if test='indexLink != null and indexLink != \"\"'>" +
            " and index_link = #{indexLink}" +
            "</if>" +
            "<if test='pcbCapacityParamKey != null and pcbCapacityParamKey != \"\"'>" +
            " and pcb_capacity_param_key = #{pcbCapacityParamKey} " +
            "</if>" +
            "<if test='pcbCapacityParamKey2 != null and pcbCapacityParamKey2 != \"\"'>" +
            " and pcb_capacity_param_key2 = #{pcbCapacityParamKey2} " +
            "</if>" +
            "<if test='pcbCapacityParamKey3 != null and pcbCapacityParamKey3 != \"\"'>" +
            " and pcb_capacity_param_key3 = #{pcbCapacityParamKey3} " +
            "</if>" +
            "<if test='level != null '>" +
            " and level = #{level}" +
            "</if>" +
            "<if test='isRequired != null '>" +
            " and is_required = #{isRequired}" +
            "</if>" +
            "<if test='paramType != null '>" +
            " and param_type = #{paramType}" +
            "</if>" +
            "<if test='paramOptions != null and paramOptions != \"\"'>" +
            " and param_options = #{paramOptions}" +
            "</if>" +
            "<if test='status != null '>" +
            " and status = #{status}" +
            "</if>" +
            "<if test='sort != null '>" +
            " and sort = #{sort} " +
            "</if>" +
            "</where>" +
            "order by sort,id" +
            "</script>")
    @ResultMap(value = "pcbCapacityParamHistoryResultMap")
    List<PcbCapacityParamHistory> getAllByLimit2(PcbCapacityParamHistory param);

    /*
     *新增所有列
     */
    @Insert("insert into " + DB2TableName.PCB_CAPAICTY_PARAM_HISTORY + " (version, pcb_capacity_param_id, parent_id, pcb_capacity_param_name, pcb_capacity_param_name_en, index_link, pcb_capacity_param_key, pcb_capacity_param_key2, pcb_capacity_param_key3, pcb_capacity_param_key4,level, is_required, param_type, param_options, rule_type, unit, tip, remark, remark_pic, create_user, create_time, update_user, update_time, status, sort) values " +
            "(#{version}, #{pcbCapacityParamId}, #{parentId}, #{pcbCapacityParamName}, #{pcbCapacityParamNameEn}, #{indexLink}, #{pcbCapacityParamKey}, #{pcbCapacityParamKey2}, #{pcbCapacityParamKey3}, #{pcbCapacityParamKey4}, #{level}, #{isRequired}, #{paramType}, #{paramOptions}, #{ruleType}, #{unit}, #{tip}, #{remark},#{remarkPic}, #{createUser}, #{createTime}, #{updateUser}, #{updateTime}, #{status}, #{sort})")
    // 显式设置主键
    @Options(useGeneratedKeys = true, keyProperty = "id")
    Integer insert(PcbCapacityParamHistory param);


    @Insert("<script>" +
            "insert into " + DB2TableName.PCB_CAPAICTY_PARAM_HISTORY + " (version, pcb_capacity_param_id, parent_id, pcb_capacity_param_name, pcb_capacity_param_name_en, index_link, pcb_capacity_param_key, pcb_capacity_param_key2, pcb_capacity_param_key3, pcb_capacity_param_key4, level, is_required, param_type, param_options, rule_type, unit, tip, remark, remark_pic, create_user, create_time, update_user, update_time, status, sort) values " +
            "<foreach collection=\"entities\" item=\"param\" separator=\",\">" +
            "(#{param.version}, #{param.pcbCapacityParamId}, #{param.parentId}, #{param.pcbCapacityParamName}, #{param.pcbCapacityParamNameEn}, #{param.indexLink}, #{param.pcbCapacityParamKey}, #{param.pcbCapacityParamKey2}, #{param.pcbCapacityParamKey3}, #{param.pcbCapacityParamKey4}, #{param.level}, #{param.isRequired}, #{param.paramType}, #{param.paramOptions}, #{param.ruleType},#{param.unit}, #{param.tip}, #{param.remark},#{param.remarkPic},#{param.createUser}, #{param.createTime}, #{param.updateUser}, #{param.updateTime}, #{param.status}, #{param.sort})" +
            "</foreach>" +
            "</script>")
    Integer insertBatch(@Param("entities") List<PcbCapacityParamHistory> entities);


    /*
     *通过版本删除
     */
    @Delete("delete from " + DB2TableName.PCB_CAPAICTY_PARAM_HISTORY + " where version = #{version} ")
    Integer deleteByVersion(@Param("version") String version);


}
